package dao;

import bean.Bicycle;
import bean.User;
import util.Connect;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * The type Bicycle dao.
 *
 * @author liu
 */
public class BicycleDAO {

    /**
     * 获取总记录数
     *
     * @return the total
     */
    public int getTotal() {
        String sql = " SELECT count(*) FROM bicycle ";
        Connection connection = Connect.getInstance().getConnection();
        int count = 0;
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Connect.getInstance().freeConnection(connection);
        return count;
    }

    /**
     * 分页查询
     *
     * @param start    the start
     * @param pagesize the pagesize
     * @return the page
     */
    public List<Bicycle> getPage(int start, int pagesize) {
        String sql = " SELECT `bicycleId`, `from`, `time`, `photoUrl`, `lockId`, `locationX`, `locationY`, bicycle.`status`, `energy`, `userName` " +
            " FROM bicycle LEFT JOIN user ON  `from` = userId  LIMIT ? , ? ";
        Connection connection = Connect.getInstance().getConnection();
        List<Bicycle> bicycleList = new ArrayList<>();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, pagesize);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Bicycle bicycle = new Bicycle();
                bicycle.setBicycleid(resultSet.getInt("bicycleId"));
                bicycle.setFrom(resultSet.getInt("from"));
                bicycle.setTime(resultSet.getTimestamp("time"));
                bicycle.setPhotourl(resultSet.getString("photoUrl"));
                bicycle.setLockid(resultSet.getInt("lockId"));
                bicycle.setLocationx(resultSet.getBigDecimal("locationX"));
                bicycle.setLocationy(resultSet.getBigDecimal("locationY"));
                bicycle.setStatus(resultSet.getInt("status"));
                bicycle.setUserName(resultSet.getString("userName"));
                bicycleList.add(bicycle);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Connect.getInstance().freeConnection(connection);
        return bicycleList;
    }

    /**
     * 按编号查询
     *
     * @param bicycleid the bicycleid
     * @return the bicycle
     */
    public Bicycle selectById(int bicycleid) {
        String sql = " SELECT `bicycleId`, `from`, `time`, `photoUrl`, `lockId`, `locationX`, `locationY`, bicycle.`status`, `energy`, `userName` " +
            " FROM bicycle JOIN user ON  `from` = userId  AND bicycleId= ? ";
        Connection connection = Connect.getInstance().getConnection();
        Bicycle bicycle = new Bicycle();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, bicycleid);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                System.out.println("bid " + resultSet.getInt("bicycleId"));
                bicycle.setBicycleid(resultSet.getInt("bicycleId"));
                System.out.println("from " + resultSet.getInt("from"));
                bicycle.setFrom(resultSet.getInt("from"));
                bicycle.setTime(resultSet.getTimestamp("time"));
                bicycle.setPhotourl(resultSet.getString("photoUrl"));
                bicycle.setLockid(resultSet.getInt("lockId"));
                bicycle.setLocationx(resultSet.getBigDecimal("locationX"));
                bicycle.setLocationy(resultSet.getBigDecimal("locationY"));
                bicycle.setStatus(resultSet.getInt("status"));
                bicycle.setUserName(resultSet.getString("userName"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Connect.getInstance().freeConnection(connection);
        return bicycle;
    }

    /**
     * 按锁编号查询
     *
     * @param lockid the lockid
     * @return the bicycle
     */
    public Bicycle selectByLockId(int lockid) {
        String sql = " SELECT `bicycleId`, `from`, `time`, `photoUrl`, `lockId`, `locationX`, `locationY`, bicycle.`status`, `energy`, `userName` " +
            " FROM bicycle JOIN user ON  `from` = userId  AND lockId= ? ";
        Connection connection = Connect.getInstance().getConnection();
        Bicycle bicycle = new Bicycle();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, lockid);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                bicycle.setBicycleid(resultSet.getInt("bicycleId"));
                bicycle.setFrom(resultSet.getInt("from"));
                bicycle.setTime(resultSet.getTimestamp("time"));
                bicycle.setPhotourl(resultSet.getString("photoUrl"));
                bicycle.setLockid(resultSet.getInt("lockId"));
                bicycle.setLocationx(resultSet.getBigDecimal("locationX"));
                bicycle.setLocationy(resultSet.getBigDecimal("locationY"));
                bicycle.setStatus(resultSet.getInt("status"));
                bicycle.setUserName(resultSet.getString("userName"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Connect.getInstance().freeConnection(connection);
        return bicycle;
    }

    /**
     * 修改信息
     *
     * @param id     the id
     * @param lockId the lock id
     * @param status the status
     * @return the int
     */
    public int updateByIdSelective(Integer id, Integer lockId, Integer status) {
        String sql = " UPDATE bicycle SET lockId = ? , status = ?  WHERE  bicycleId = ? ";
        Connection connection = Connect.getInstance().getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            return 0;
        }
        int success = 0;
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, lockId);
            ps.setInt(2, status);
            ps.setInt(3, id);
            success = ps.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                success = 0;
            }
        }
        return success;
    }

    /**
     * 删除
     *
     * @param bicycleid the bicycleid
     * @return the int
     */
    public int deleteById(int bicycleid) {
        String sql = " DELETE FROM bicycle  WHERE  bicycleId = ? ";
        Connection connection = Connect.getInstance().getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            return 0;
        }
        int success = 0;
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, bicycleid);
            success = ps.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                success = 0;
            }
        }
        return success;
    }

    /**
     * 动态请求
     *
     * @param prefix the prefix
     * @return the list
     */
    public List<Integer> selectByIdPrefix(String prefix) {
        String sql = "select " +
            "        bicycleId " +
            "        from bicycle " +
            "        where cast(bicycleId as char) like \'" + prefix + "%\'";
        Connection connection = Connect.getInstance().getConnection();
        List<Integer> bicycleIdList = new ArrayList<>();
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                bicycleIdList.add(rs.getInt("bicycleId"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Connect.getInstance().freeConnection(connection);
        return bicycleIdList;
    }

    /**
     * Select by lock id prefix list.
     *
     * @param prefix the prefix
     * @return the list
     */
    public List<Integer> selectByLockIdPrefix(String prefix) {
        String sql = " select " +
            "        lockId " +
            "        from bicycle " +
            "        where cast(lockId as char) like \'" + prefix + "%\'";
        Connection connection = Connect.getInstance().getConnection();
        List<Integer> bicycleIdList = new ArrayList<>();
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                bicycleIdList.add(rs.getInt("lockId"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Connect.getInstance().freeConnection(connection);
        return bicycleIdList;
    }

    /**
     * 增加车辆
     *
     * @param bicycle the bicycle
     * @return the int
     */
    public int addBicycle(Bicycle bicycle) {
        StringBuilder sql = new StringBuilder("insert into bicycle(  `from`,`time`,photoUrl,lockId,locationX,locationY,`status`,energy)  values( ?,current_timestamp(),?,?,?,?,?,?)");
        Connection connection = Connect.getInstance().getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            return 0;
        }
        boolean result = false;
        try {
            PreparedStatement ps = connection.prepareStatement(String.valueOf(sql));
            ps.setInt(1, bicycle.getFrom());
            ps.setString(2, bicycle.getPhotourl());
            ps.setInt(3, bicycle.getLockid());
            ps.setBigDecimal(4, bicycle.getLocationx());
            ps.setBigDecimal(5, bicycle.getLocationy());
            ps.setInt(6, bicycle.getStatus());
            ps.setFloat(7, bicycle.getEnergy());
            result = ps.execute();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                result = false;
            }
        }
        return result ? 1 : 0;
    }

    /**
     * Gets user id.
     *
     * @param username the username
     * @return the user id
     */
    public int getUserId(String username) {
        String sql = "SELECT userId FROM user WHERE userName= ?";
        Connection connection = Connect.getInstance().getConnection();
        PreparedStatement ps;
        int userId = -1;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, username);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                userId = resultSet.getInt(1);
            }
            Connect.getInstance().freeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userId;
    }

    /**
     * User add int.
     *
     * @param user the user
     * @return the int
     */
    public int userAdd(User user) {
        StringBuffer sql = new StringBuffer("insert into user(  `userName`,`password`,`name`,`phone`,`balance`)  values( ?,?,?,?,? )");
        Connection connection = Connect.getInstance().getConnection();
        int row;
        int userId = -1;
        try {
            PreparedStatement ps = connection.prepareStatement(String.valueOf(sql), Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getName());
            ps.setString(4, user.getPhone());
            ps.setBigDecimal(5, BigDecimal.valueOf(Double.valueOf(user.getBalance())));
            row = ps.executeUpdate();
            if (row > 0) {
                ResultSet rs = ps.getGeneratedKeys();
                rs.next();
                userId = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userId;
    }

    /**
     * Gets nearby bike.
     *
     * @param maxLat the max lat
     * @param minLat the min lat
     * @param maxLng the max lng
     * @param minLng the min lng
     * @return the nearby bike
     */
    public List<Bicycle> getNearbyBike(double maxLat, double minLat, double maxLng, double minLng) {
        String sql = " SELECT `bicycleId`, `from`, `time`, `photoUrl`, `lockId`, `locationX`, `locationY`, bicycle.`status`, `energy` " +
            " FROM bicycle WHERE ((locationX BETWEEN ? AND ?) AND (locationY BETWEEN ? AND ?))";
        Connection connection = Connect.getInstance().getConnection();
        List<Bicycle> bicycleList = new ArrayList<>();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setBigDecimal(1, new BigDecimal(minLat));
            ps.setBigDecimal(2, new BigDecimal(maxLat));
            ps.setBigDecimal(3, new BigDecimal(maxLng));
            ps.setBigDecimal(4, new BigDecimal(minLng));
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Bicycle bicycle = new Bicycle();
                bicycle.setBicycleid(resultSet.getInt("bicycleId"));
                bicycle.setFrom(resultSet.getInt("from"));
                bicycle.setTime(resultSet.getTimestamp("time"));
                bicycle.setPhotourl(resultSet.getString("photoUrl"));
                bicycle.setLockid(resultSet.getInt("lockId"));
                bicycle.setLocationx(resultSet.getBigDecimal("locationX"));
                bicycle.setLocationy(resultSet.getBigDecimal("locationY"));
                bicycle.setStatus(resultSet.getInt("status"));

                bicycleList.add(bicycle);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Connect.getInstance().freeConnection(connection);
        return bicycleList;
    }

    public Optional<Bicycle> findBycicleById(String lockId) throws SQLException {
        String sql = "SELECT bicycleId, `from`, time, photoUrl, lockId, locationX, locationY, status, energy FROM bicycle WHERE lockId = ?";
        Connect connect = Connect.getInstance();
        Connection connection = connect.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, lockId);
        ResultSet rs = ps.executeQuery();
        Bicycle bicycle = null;
        if (rs.next()) {
            bicycle = new Bicycle();
            bicycle.setBicycleid(rs.getInt("bicycleId"));
            bicycle.setFrom(rs.getInt("from"));
            bicycle.setTime(rs.getTimestamp("time"));
            bicycle.setPhotourl(rs.getString("photoUrl"));
            bicycle.setLockid(rs.getInt("lockId"));
            bicycle.setLocationx(rs.getBigDecimal("locationX"));
            bicycle.setLocationy(rs.getBigDecimal("locationY"));
            bicycle.setStatus(rs.getInt("status"));
            bicycle.setEnergy(rs.getFloat("energy"));
        }
        connect.freeResultSet(rs);
        connect.freeStatement(ps);
        connect.freeConnection(connection);
        return Optional.ofNullable(bicycle);
    }
}
